package com.ph.irp.base.stockprice.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.stockprice.dao.StockPriceDAO;
import com.ph.irp.model.PBaseStockprice;
import com.ph.util.DBUtil;

public class StockPriceDAOImpl implements StockPriceDAO {
	
	//数据源
	private ComboPooledDataSource dataSource;  
	//setter for dataSource
	public void setDataSource(ComboPooledDataSource dataSource){
		
		this.dataSource = dataSource ;
		
	}
	
	/**
	 * 删除采购价格`
	 */
	@Override
	public void deleteStockPriceByIds(Object[] stockPriceIds) throws Throwable{
		// TODO Auto-generated method stub
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//删除sql语句
		String sql = "delete from p_base_stockprice where stockPriceID=?"  ;		
		try {
			
			con = this.dataSource.getConnection();
			
			for(Object object :stockPriceIds){
			
				int stockpriceId = Integer.valueOf((String) object) ;
			
				statement =con.prepareStatement(sql);
			
				statement.setInt(1, stockpriceId);
				
				statement.execute();	
			}
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
	}

	/**
	 * 查询所有的采购价格
	 */
	@Override
	public List<PBaseStockprice> getAllStockPrices() throws Throwable{
		// TODO Auto-generated method stub
		//销售价格列表
		List<PBaseStockprice> stockpriceModelList = new ArrayList<PBaseStockprice>();
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//查询语句
		String sql = "select * from p_base_stockprice " ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			
			resultSet = statement.executeQuery() ;
			//处理结果集中的记录到采购价格model
			while(resultSet.next()){
				PBaseStockprice stockpriceModel = new PBaseStockprice();
				
				stockpriceModel.setStockPriceId(resultSet.getInt(1)) ;
				
				stockpriceModel.setPBaseVerifyprojectMaterialId(resultSet.getInt(2)) ;
				
				stockpriceModel.setPBaseVerifyprojectSupplierId(resultSet.getInt(3)) ;
				
				stockpriceModel.setStockNumberFrom(resultSet.getFloat(4)) ;
				
				stockpriceModel.setStockNumberTo(resultSet.getFloat(5)) ;
				
				stockpriceModel.setBid(resultSet.getFloat(6)) ;
				
				stockpriceModel.setAuditState(resultSet.getInt(7)) ;
				
				stockpriceModel.setTopBid(resultSet.getFloat(8)) ;
				
				stockpriceModel.setMemo(resultSet.getString(9)) ;
				
				stockpriceModelList.add(stockpriceModel) ;
				
			}
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		return stockpriceModelList;
	}
	
	/**
	 * 根据id查询采购价格
	 */
	@Override
	public PBaseStockprice getStockPriceById(Integer stockPriceId) throws Throwable{
		// TODO Auto-generated method stub
		PBaseStockprice stockpriceModel = new PBaseStockprice();
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//查询语句
		String sql = "select * from p_base_stockprice where StockPriceID=?" ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setInt(1,stockPriceId) ;
			resultSet = statement.executeQuery() ;
			//处理结果集中的记录到采购价格model
			while(resultSet.next()){
				
				stockpriceModel.setStockPriceId(resultSet.getInt(1)) ;
				
				stockpriceModel.setPBaseVerifyprojectMaterialId(resultSet.getInt(2)) ;
				
				stockpriceModel.setPBaseVerifyprojectSupplierId(resultSet.getInt(3)) ;
				
				stockpriceModel.setStockNumberFrom(resultSet.getFloat(4)) ;
				
				stockpriceModel.setStockNumberTo(resultSet.getFloat(5)) ;
				
				stockpriceModel.setBid(resultSet.getFloat(6)) ;
				
				stockpriceModel.setAuditState(resultSet.getInt(7)) ;
				
				stockpriceModel.setTopBid(resultSet.getFloat(8)) ;
				
				stockpriceModel.setMemo(resultSet.getString(9)) ;			
			}
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		
		return stockpriceModel;
	}
	/**
	 * 根据供应商id查询相应的采购价格资料
	 */
	@Override
	public List<PBaseStockprice> getStockPriceBySupplierId(int supplierId)throws Throwable{	
		//销售价格列表
		List<PBaseStockprice> stockpriceModelList = new ArrayList<PBaseStockprice>();
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//查询语句
		String sql = "select * from p_base_stockprice where supplierId=?" ;	
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setInt(1, supplierId) ;
			resultSet = statement.executeQuery() ;
			//处理结果集中的记录到采购价格model
			while(resultSet.next()){
				PBaseStockprice stockpriceModel = new PBaseStockprice();
				
				stockpriceModel.setStockPriceId(resultSet.getInt(1)) ;
				
				stockpriceModel.setPBaseVerifyprojectMaterialId(resultSet.getInt(2)) ;
				
				stockpriceModel.setPBaseVerifyprojectSupplierId(resultSet.getInt(3)) ;
				
				stockpriceModel.setStockNumberFrom(resultSet.getFloat(4)) ;
				
				stockpriceModel.setStockNumberTo(resultSet.getFloat(5)) ;
				
				stockpriceModel.setBid(resultSet.getFloat(6)) ;
				
				stockpriceModel.setAuditState(resultSet.getInt(7)) ;
				
				stockpriceModel.setTopBid(resultSet.getFloat(8)) ;
				
				stockpriceModel.setMemo(resultSet.getString(9)) ;
				
				stockpriceModelList.add(stockpriceModel) ;
				
			}
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		return stockpriceModelList;
	}
	/**
	 * 根据物料id查询相应的采购价格资料
	 */
	@Override
	public List<PBaseStockprice> getStockPriceByMaterialId(int materialId) throws Throwable{
		// TODO Auto-generated method stub
		//销售价格列表
		List<PBaseStockprice> stockpriceModelList = new ArrayList<PBaseStockprice>();
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//查询语句
		String sql = "select * from p_base_stockprice where materialId=?" ;	
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setInt(1, materialId) ;
			resultSet = statement.executeQuery() ;
			//处理结果集中的记录到采购价格model
			while(resultSet.next()){
				PBaseStockprice stockpriceModel = new PBaseStockprice();
				
				stockpriceModel.setStockPriceId(resultSet.getInt(1)) ;
				
				stockpriceModel.setPBaseVerifyprojectMaterialId(resultSet.getInt(2)) ;
				
				stockpriceModel.setPBaseVerifyprojectSupplierId(resultSet.getInt(3)) ;
				
				stockpriceModel.setStockNumberFrom(resultSet.getFloat(4)) ;
				
				stockpriceModel.setStockNumberTo(resultSet.getFloat(5)) ;
				
				stockpriceModel.setBid(resultSet.getFloat(6)) ;
				
				stockpriceModel.setAuditState(resultSet.getInt(7)) ;
				
				stockpriceModel.setTopBid(resultSet.getFloat(8)) ;
				
				stockpriceModel.setMemo(resultSet.getString(9)) ;
				
				stockpriceModelList.add(stockpriceModel) ;
				
			}
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		return stockpriceModelList;
	}
	/**
	 * 根据供应商id和物料id查询相应的采购价格资料
	 */
	@Override
	public List<PBaseStockprice> getStockPriceBySupplierIdAndMaterialId(
			int supplierId, int materialId) throws Throwable{
		// TODO Auto-generated method stub
		//销售价格列表
		List<PBaseStockprice> stockpriceModelList = new ArrayList<PBaseStockprice>();
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//查询语句
		String sql = "select * from p_base_stockprice where supplierId=? and materialid=?" ;	
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setInt(1, supplierId);
			statement.setInt(2, materialId);
			resultSet = statement.executeQuery() ;
			//处理结果集中的记录到采购价格model
			while(resultSet.next()){
				PBaseStockprice stockpriceModel = new PBaseStockprice();
				
				stockpriceModel.setStockPriceId(resultSet.getInt(1)) ;
				
				stockpriceModel.setPBaseVerifyprojectMaterialId(resultSet.getInt(2)) ;
				
				stockpriceModel.setPBaseVerifyprojectSupplierId(resultSet.getInt(3)) ;
				
				stockpriceModel.setStockNumberFrom(resultSet.getFloat(4)) ;
				
				stockpriceModel.setStockNumberTo(resultSet.getFloat(5)) ;
				
				stockpriceModel.setBid(resultSet.getFloat(6)) ;
				
				stockpriceModel.setAuditState(resultSet.getInt(7)) ;
				
				stockpriceModel.setTopBid(resultSet.getFloat(8)) ;
				
				stockpriceModel.setMemo(resultSet.getString(9)) ;
				
				stockpriceModelList.add(stockpriceModel) ;
				
			}
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		return stockpriceModelList;
	}
	
	/**
	 * 根据采购价格资料状态查询采购价格
	 */
	@Override
	public List<PBaseStockprice> getStockPricesByState(int state) throws Throwable{
		// TODO Auto-generated method stub
		//销售价格列表
		List<PBaseStockprice> stockpriceModelList = new ArrayList<PBaseStockprice>();
		
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//查询语句
		String sql = "select * from p_base_stockprice where AuditState=?" ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setInt(1, state) ;
			resultSet = statement.executeQuery() ;
			//处理结果集中的记录到采购价格model
			while(resultSet.next()){
				PBaseStockprice stockpriceModel = new PBaseStockprice();
				
				stockpriceModel.setStockPriceId(resultSet.getInt(1)) ;
				
				stockpriceModel.setPBaseVerifyprojectMaterialId(resultSet.getInt(2)) ;
				
				stockpriceModel.setPBaseVerifyprojectSupplierId(resultSet.getInt(3)) ;
				
				stockpriceModel.setStockNumberFrom(resultSet.getFloat(4)) ;
				
				stockpriceModel.setStockNumberTo(resultSet.getFloat(5)) ;
				
				stockpriceModel.setBid(resultSet.getFloat(6)) ;
				
				stockpriceModel.setAuditState(resultSet.getInt(7)) ;
				
				stockpriceModel.setTopBid(resultSet.getFloat(8)) ;
				
				stockpriceModel.setMemo(resultSet.getString(9)) ;	
				
				stockpriceModelList.add(stockpriceModel) ;
			}
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		
		return stockpriceModelList;
	
	}
	/**
	 * 更新采购价格
	 */
	@Override
	public void updateStockPrice(PBaseStockprice pbaseStockPriceModel) throws Throwable{
		// TODO Auto-generated method stub
	
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//修改sql语句
		StringBuilder sql = new StringBuilder();
		
		sql.append("update p_base_stockprice set MaterialID=");
		sql.append(pbaseStockPriceModel.getPBaseVerifyprojectMaterialId()+ ",");
		sql.append("SupplierID="+pbaseStockPriceModel.getPBaseVerifyprojectSupplierId()+",") ;
		sql.append("StockNumberFrom=" +pbaseStockPriceModel.getStockNumberFrom()+",") ;
		sql.append("StockNumberTo="+ pbaseStockPriceModel.getStockNumberTo()+",") ;
		sql.append("Bid=" + pbaseStockPriceModel.getBid()+",") ;
		sql.append("AuditState=" + pbaseStockPriceModel.getAuditState()+",") ;
		sql.append("TopBid=" +pbaseStockPriceModel.getTopBid()+",");
		sql.append("Memo='"+pbaseStockPriceModel.getMemo());
		sql.append("' where stockpriceID="+pbaseStockPriceModel.getStockPriceId()) ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql.toString()) ;			
			statement.executeUpdate() ;		
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
	
	}
	
	/**
	 * 新增采购价格
	 */
	@Override
	public void addStockPrice(PBaseStockprice pbaseStockPriceModel) throws Throwable{
		// TODO Auto-generated method stub
	
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//新增sql语句
		StringBuilder sql = new StringBuilder();
		
		sql.append("insert into p_base_stockprice(MaterialID,SupplierID,StockNumberFrom,StockNumberTo,Bid,AuditState,TopBid,Memo) ") ;
		sql.append("values(") ;
		sql.append(pbaseStockPriceModel.getPBaseVerifyprojectMaterialId()+",") ;
		sql.append(pbaseStockPriceModel.getPBaseVerifyprojectSupplierId()+",") ;
		sql.append(pbaseStockPriceModel.getStockNumberFrom()+",") ;
		sql.append(pbaseStockPriceModel.getStockNumberTo()+",") ;
		sql.append(pbaseStockPriceModel.getBid()+",") ;
		sql.append(pbaseStockPriceModel.getAuditState()+",") ;
		sql.append(pbaseStockPriceModel.getTopBid()+",'");
		sql.append(pbaseStockPriceModel.getMemo()+"')");
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql.toString()) ;
			
			statement.executeUpdate() ;
			
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		
	}
	/**
	 * 审核采购价格资料
	 */
	@Override
	public void checkStockPrice(Integer stockPriceId) throws Throwable{
		// TODO Auto-generated method stub
		
	
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//审核sql语句
		StringBuilder sql = new StringBuilder();
		sql.append("update p_base_stockprice set AuditState=1 where StockPriceID=") ;
		sql.append(stockPriceId) ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql.toString()) ;
			
			statement.executeUpdate() ;
			
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		
	}
	/**
	 * 反审核采购价格资料
	 */
	@Override
	public void uncheckStockPrice(Integer stockPriceId) throws Throwable{
		// TODO Auto-generated method stub
		
	
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//审核sql语句
		StringBuilder sql = new StringBuilder();
		sql.append("update p_base_stockprice set AuditState=0 where StockPriceID=") ;
		sql.append(stockPriceId) ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql.toString()) ;
			
			statement.executeUpdate() ;		
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
		
	}
	/**
	 *对某一个供应商的某一物料限价
	 */
	public void conTrolPrice(int materialId,float topTip,int supplierId)throws Throwable{
		
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//限价sql语句

		String sql = "update p_base_stockprice set topBid=? where supplierid=? and materialId=?";
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setFloat(1,topTip) ;
			statement.setInt(2, supplierId) ;
			statement.setInt(3, materialId) ;
			//执行更新语句
			statement.executeUpdate() ;
			
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
	}
	/**
	 * 对所有供应商的某一物料进行限价
	 */
	@Override
	public void conTrolPrice(int materialId, float topTip) throws Throwable{
		// TODO Auto-generated method stub
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//限价sql语句

		String sql = "update p_base_stockprice set topBid=? where materialId=?";
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql) ;
			statement.setFloat(1,topTip);
			statement.setInt(2, materialId);
			//执行更新语句
			statement.executeUpdate() ;
			
			
		}catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
	}
	
	
	
}
